﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CarLoanSystem.Entity;
using System.Data.SqlClient;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class ScheduleInfoDAL : DBBase
    {
        public static ScheduleItem LoadScheduleLoan(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [SCHEDULE_ID] ,[LOAN_ID] ,[EMPLOYEE_ID] ,[SCHEDULE_JOB] ,[SCHEDULE_STATUS]
                FROM [CAR_LOAN].[dbo].[SCHEDULE] where [LOAN_ID] = @LOAN_ID AND [SCHEDULE_JOB] = 'L'", DBBase.con);
                command.Parameters.AddWithValue("@LOAN_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    ScheduleItem item = new ScheduleItem();
                    item.EMPLOYEE_ID = Convert.ToInt32(dt.Rows[0]["EMPLOYEE_ID"].ToString());
                    item.LOAN_ID = Convert.ToInt32(dt.Rows[0]["LOAN_ID"].ToString());
                    item.SCHEDULE_ID = Convert.ToInt32(dt.Rows[0]["SCHEDULE_ID"].ToString());
                    item.SCHEDULE_JOB = dt.Rows[0]["SCHEDULE_JOB"].ToString().ToUpper();
                    item.SCHEDULE_STATUS = dt.Rows[0]["SCHEDULE_STATUS"].ToString();
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static void Insert(ScheduleItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[SCHEDULE]
                                                       ([LOAN_ID]
                                                       ,[EMPLOYEE_ID]
                                                       ,[SCHEDULE_JOB]
                                                       ,[SCHEDULE_STATUS])
                                                 VALUES
                                                      ( @LOAN_ID
                                                       ,@EMPLOYEE_ID
                                                       ,@SCHEDULE_JOB
                                                       ,@SCHEDULE_STATUS)", DBBase.con);

                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                command.Parameters.AddWithValue("@EMPLOYEE_ID", item.EMPLOYEE_ID);
                command.Parameters.AddWithValue("@SCHEDULE_JOB", item.SCHEDULE_JOB.ToUpper());
                command.Parameters.AddWithValue("@SCHEDULE_STATUS", item.SCHEDULE_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void Update(ScheduleItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[SCHEDULE]
                                                    SET [SCHEDULE_STATUS] = @SCHEDULE_STATUS
                                                  WHERE ([LOAN_ID] = @LOAN_ID )"
                                                    , DBBase.con);

                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                command.Parameters.AddWithValue("@SCHEDULE_STATUS", item.SCHEDULE_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static int LoadEmpJobLoan()
        {
            try
            {
                string sqlQuery = @"(SELECT E.[EMPLOYEE_ID],COUNT(S.EMPLOYEE_ID) as amount
                FROM [CAR_LOAN].[dbo].EMPLOYEE E LEFT JOIN [CAR_LOAN].[dbo].[SCHEDULE] S 
                ON E.EMPLOYEE_ID = S.EMPLOYEE_ID
                where (S.[SCHEDULE_STATUS] = 'A' and E.EMPLOYEE_POSITION = 's' AND S.SCHEDULE_JOB = 'L')
                group by E.[EMPLOYEE_ID])
                UNION
                (SELECT E.[EMPLOYEE_ID],0 as amount
                FROM [CAR_LOAN].[dbo].EMPLOYEE E LEFT JOIN [CAR_LOAN].[dbo].[SCHEDULE] S 
                ON E.EMPLOYEE_ID <> S.EMPLOYEE_ID
                where (S.[SCHEDULE_STATUS] = 'A' AND E.EMPLOYEE_POSITION = 's' AND S.SCHEDULE_JOB = 'L')
                group by E.[EMPLOYEE_ID])
                ORDER BY amount";

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0]["EMPLOYEE_ID"].ToString());
                }
                else 
                {
                    sqlQuery = @"SELECT [EMPLOYEE_ID] FROM [CAR_LOAN].[dbo].[EMPLOYEE] WHERE [EMPLOYEE_POSITION] = 's'";

                    DBBase.ConncetDatabase();

                    command = new SqlCommand(sqlQuery, DBBase.con);
                    dr = command.ExecuteReader();

                    dt = new DataTable();
                    if (dr.HasRows)
                    {
                        dt.Load(dr);
                    }
                    if (dt.Rows.Count > 0)
                    {
                        return Convert.ToInt32(dt.Rows[0]["EMPLOYEE_ID"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
            DBBase.DisConncetDatabase();
            return 0;
        }

        public static int LoadEmpJobDept()
        {
            try
            {
                string sqlQuery = @"(SELECT E.[EMPLOYEE_ID],COUNT(S.EMPLOYEE_ID) as amount
                FROM [CAR_LOAN].[dbo].EMPLOYEE E LEFT JOIN [CAR_LOAN].[dbo].[SCHEDULE] S 
                ON E.EMPLOYEE_ID = S.EMPLOYEE_ID
                where (S.[SCHEDULE_STATUS] = 'A' and E.EMPLOYEE_POSITION = 'd' AND S.SCHEDULE_JOB = 'D')
                group by E.[EMPLOYEE_ID])
                UNION
                (SELECT E.[EMPLOYEE_ID],0 as amount
                FROM [CAR_LOAN].[dbo].EMPLOYEE E LEFT JOIN [CAR_LOAN].[dbo].[SCHEDULE] S 
                ON E.EMPLOYEE_ID <> S.EMPLOYEE_ID
                where (S.[SCHEDULE_STATUS] = 'A' AND E.EMPLOYEE_POSITION = 'd' AND S.SCHEDULE_JOB = 'D')
                group by E.[EMPLOYEE_ID])
                ORDER BY amount";

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0]["EMPLOYEE_ID"].ToString());
                }
                else
                {
                    sqlQuery = @"SELECT [EMPLOYEE_ID] FROM [CAR_LOAN].[dbo].[EMPLOYEE] WHERE [EMPLOYEE_POSITION] = 'd'";

                    DBBase.ConncetDatabase();

                    command = new SqlCommand(sqlQuery, DBBase.con);
                    dr = command.ExecuteReader();

                    dt = new DataTable();
                    if (dr.HasRows)
                    {
                        dt.Load(dr);
                    }
                    if (dt.Rows.Count > 0)
                    {
                        return Convert.ToInt32(dt.Rows[0]["EMPLOYEE_ID"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
            DBBase.DisConncetDatabase();
            return 0;
        }
    }
}
